<?php defined('SYSPATH') or die('No direct script access.');

class Channels_Playlists_Model extends Model{

	public function __construct() {
		$this->db = Database::instance();
	}

	public function get_channels_playLists($id){
		$sql = '
			SELECT * 
			FROM channels_playlists 
			WHERE channel_id=?;
    	';
		$query = $this->db->query($sql, array($id));
		return $query->result(FALSE);
	}

	public function get_playList_By_channelID($channelID){
		$sql = '
			SELECT playlist_id 
			FROM channels_playlists 
			WHERE channel_id=?;
    	';
		$query = $this->db->query($sql, array($channelID));
		return $query->result(FALSE);
	}

	public function get_playlists(){
		$sql = "
		SELECT 
  			playlists.id,
  			playlists.title,
  			playlists.description,
  			playlists.playlist_logo,
  			playlists.created,
  			playlists.updated,
  			playlists.updated as owner
		FROM
  			playlists";

		$query = $this->db->query($sql);
		return $query->result(FALSE);
	}

	public function get_playlists_channel($channel_id){
		$sql = "
		SELECT DISTINCT
  			`playlists`.id,
  			`playlists`.title,
  			`playlists`.description,
  			`playlists`.playlist_logo,
  			`playlists`.created,
  			`playlists`.updated
		FROM
  			`playlists`
  			INNER JOIN `channels_playlists` ON (`playlists`.id = `channels_playlists`.playlist_id)
		WHERE
  			`channels_playlists`.channel_id = ?;";

		$query = $this->db->query($sql,array($channel_id));
		return $query->result(FALSE);
	}

	public function get_playlists_user_channel($channel_id,$user_id){
		$sql = "
		SELECT DISTINCT
  			`playlists`.id,
  			`playlists`.title,
  			`playlists`.description,
  			`playlists`.playlist_logo,
  			`playlists`.created,
  			`playlists`.updated
		FROM
  			`playlists`
  			INNER JOIN `channels_playlists` ON (`playlists`.id = `channels_playlists`.playlist_id)
		WHERE
  			`channels_playlists`.channel_id = ? AND 
  			`channels_playlists`.owner_id = ? ";

		$query = $this->db->query($sql,array($channel_id,$user_id));
		return $query->result(FALSE);
	}

	public function get_playlists_user($user_id){
		$sql = "
		SELECT DISTINCT
  			`playlists`.id,
  			`playlists`.title,
  			`playlists`.description,
  			`playlists`.playlist_logo,
  			`playlists`.created,
  			`playlists`.updated
		FROM
  			`playlists`
  			INNER JOIN `channels_playlists` ON (`playlists`.id = `channels_playlists`.playlist_id)
		WHERE
  			`channels_playlists`.owner_id = ? ";

		$query = $this->db->query($sql,array($user_id));
		return $query->result(FALSE);
	}

	public function get_selected_playlist($user_id,$channel_id){
		$sql = "
			SELECT DISTINCT 
	  				`channels_playlists`.owner_id,
	  				`channels_playlists`.channel_id,
	  				`channels_playlists`.playlist_id,
	  				`channels_playlists`.created
			FROM
	  				`channels_playlists`
			WHERE
	  				`channels_playlists`.owner_id = ? AND 
	  				`channels_playlists`.channel_id = ?;";
		$query = $this->db->query($sql,array($user_id,$channel_id));
		return $query->result(FALSE);
	}

	public function get_playlist_in_channel($user_id,$channel_id){
		$sql = "
				SELECT *,
					(
 						SELECT count(*)
 						FROM channels_playlists cp  
 						WHERE cp.playlist_id=p.id
 						AND cp.owner_id=? AND cp.channel_id=?
					) as owner
				FROM playlists p;";
		$query = $this->db->query($sql,array($user_id,$channel_id));
		return $query->result(FALSE);
	}

	public function save($data,$mode="add"){
		$this->db->query('BEGIN');
		try {
			if ($mode=='add') {
				$sql = 'INSERT INTO channels_playlists(owner_id, channel_id, playlist_id, created) VALUES(?,?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($data['ownerId'],$data['title'], $data['description'], $data['logo']));
			}
			else {
				if (empty($data['logo'])) {
					//					Kohana::log("debug","UPDATE");
					$sql = 'UPDATE channels SET title=?, description=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
					$query = $this->db->query($sql, array($data['title'], $data['description'], $data['id']));
				}else{
					//					Kohana::log("debug","UPDATE with picture");
					$sql = 'UPDATE channels SET title=?, description=?, channel_picture=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
					$query = $this->db->query($sql, array($data['title'], $data['description'], $data['logo'], $data['id']));
				}
			}
			$this->db->query('COMMIT');
			return true;
		}
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}
	}

}
